import _ from 'lodash';
import db from '../../xp/db';
import * as util from '../../../util';

const myknex = db._;
const userColumn = 'id,username,name,userType,code,mobile,createTime';
const userColumns = userColumn.split(',');

export function fetch(query, op, pageOpt) {
    return db.get('xp_activity_votes').query(qb => {
        if (query) {
            util.andWhere(query, op, qb);
        }
        qb.orderBy('createTime', 'desc');
    }).fetchPage({
        pageSize: pageOpt.pageSize,
        page: pageOpt.page,
        withRelated: [{
            customer: qb => {
                qb.columns(userColumns);
            }
        }]
    });
}

export function find(query, op) {
    return db.get('xp_activity_votes').query(qb => {
        if (query) {
            util.andWhere(query, op, qb);
        }
        qb.orderBy('createTime', 'desc');
    }).list({
        withRelated: [{
            customer: qb => {
                qb.columns(userColumns);
            }
        },
            'option'
        ]
    });
}

export function top(query) {
    let n = 20; // 默认top 20
    if (_.isEmpty(query.activityId)) {
        query.activityId = '';
    }
    if (!_.isEmpty(query.n)) {
        n = query.n;
    }
    const sql = `
        SELECT * FROM (
            SELECT 
                COALESCE(tmp1.votes, 0) as votes,
                COALESCE(tmp1.rank, 
                    (SELECT COUNT(*) + 1 FROM (SELECT COUNT(optionId) FROM xp_activity_votes GROUP BY optionId) t)
                ) as rank,
                COALESCE(tmp1.orderRank, 
                    (SELECT COUNT(*) + 1 FROM (SELECT COUNT(optionId) FROM xp_activity_votes GROUP BY optionId) t)
                ) as orderRank,
                u.* FROM sys_users u
            LEFT JOIN
            (
                    select
                    tmp.optionId,tmp.votes,
                    -- 顺序一直在变大
                    COALESCE(@j:=@j+1, 0) as orderRank,
                    -- 只有在前后二次排序值不同时才会使用顺序号
                    @k:=(case when @pre_votes=tmp.votes then @k else @j end) as rank,
                    @pre_score:=tmp.votes as pre_votes
                    from 
                    (
                        -- 票数排序
                        SELECT
                            a.optionId,
                            COUNT(a.optionId) votes
                        FROM
                            xp_activity_votes a
                        WHERE a.activityId = '${query.activityId}'
                        GROUP BY a.optionId
                        ORDER BY votes DESC
                    ) tmp,
                    -- @k 表示最终的排名(相同值时序号相同） 
                    -- @j 表示顺序排名 
                    (select @k :=0,@j:=0,@pre_votes:=0) sdcore
            ) tmp1
            ON u.id = tmp1.optionId
        ) tmp2
        WHERE tmp2.orderRank <= ${n}
    `;
    return myknex.raw(sql);
}

export function myTop(query, userId) {
    if (!_.isEmpty(userId)) {
        userId = '';
    }
    if (!_.isEmpty(query.activityId)) {
        query.activityId = '';
    }
    const sql = `
        SELECT COUNT(*) + 1 AS rank FROM (
                SELECT * FROM (
                    SELECT
                                a.optionId,
                                COUNT(a.optionId) AS votes
                            FROM
                                xp_activity_votes a
                            WHERE 
                                a.activityId = '${query.activityId}'
                            GROUP BY a.optionId
                            ORDER BY votes DESC, createTime ASC
                ) t1
              WHERE votes > (SELECT COUNT(b.optionId) FROM xp_activity_votes b WHERE b.optionId = '${userId}' and b.activityId = '${query.activityId}' GROUP BY b.optionId)
                GROUP BY optionId
                ORDER BY votes DESC
        ) t2
    `;
    return myknex.raw(sql);
}

/**
 * 添加投票，当输入的客户信息已经存在时修改，没有时新增
 * */
export function add(data, customer) {
    return db.exec(model => (model => {
        return db.get('xp_activity_votes').query(qb => {
            qb.where({id: customer.id});
        }).list().then(res => {
            res = res.toJSON();
            if (res.length > 0) {
                return model('xp_customers').where({id: customer.id}).save(customer, {method: 'update'});
            }
            return model('xp_customers').forge().save(customer, {method: 'insert'});
        });
    })
        .then(() => model('xp_activity_votes').forge().save(data, {method: 'insert'})));
}

export function edit(data) {
    return db.exec(model => model('xp_activity_votes').where({id: data.id}).save(data, {method: 'update'}));
}

export function del(id) {
    return db.exec(
        model => model('xp_activity_votes').where({id}).drop()
    );
}
